"""Update alarm schema to match new structure.

Revision ID: 20251027_update
Revises: 20251018_1550
Create Date: 2025-10-27 00:00:00

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '20251027_update'
down_revision = '20251018_1550'
depends_on = None


def upgrade() -> None:
    """Update alarm tables to match new schema."""
    # Drop old alarm tables if they exist
    op.execute('DROP TABLE IF EXISTS alarm_history')
    op.execute('DROP TABLE IF EXISTS alarm_rules')
    op.execute('DROP TYPE IF EXISTS alarmseverity')
    op.execute('DROP TYPE IF EXISTS alarmtype')

    # Create alarm_rules table with new schema
    op.create_table(
        'alarm_rules',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('device_id', sa.Integer(), nullable=False),
        sa.Column('device_name', sa.String(255), nullable=False),
        sa.Column('metric_name', sa.String(255), nullable=False),
        sa.Column('operator', sa.String(10), nullable=False),  # gt, lt, eq, gte, lte, ne
        sa.Column('threshold', sa.Float(), nullable=False),
        sa.Column('severity', sa.String(20), nullable=False),  # critical, warning, info
        sa.Column('duration', sa.Integer(), nullable=False, server_default='0'),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default='1'),
        sa.Column('description', sa.String(500), nullable=True),
        sa.Column('created_by_id', sa.Integer(), nullable=False),
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.ForeignKeyConstraint(['created_by_id'], ['users.id']),
        sa.PrimaryKeyConstraint('id')
    )

    # Create indexes for alarm_rules
    op.create_index('idx_alarm_rules_device_metric', 'alarm_rules', ['device_id', 'metric_name', 'is_active'])
    op.create_index(op.f('ix_alarm_rules_id'), 'alarm_rules', ['id'])
    op.create_index(op.f('ix_alarm_rules_device_id'), 'alarm_rules', ['device_id'])
    op.create_index(op.f('ix_alarm_rules_device_name'), 'alarm_rules', ['device_name'])
    op.create_index(op.f('ix_alarm_rules_metric_name'), 'alarm_rules', ['metric_name'])
    op.create_index(op.f('ix_alarm_rules_severity'), 'alarm_rules', ['severity'])
    op.create_index(op.f('ix_alarm_rules_is_active'), 'alarm_rules', ['is_active'])
    op.create_index(op.f('ix_alarm_rules_created_by_id'), 'alarm_rules', ['created_by_id'])

    # Create alarm_history table with new schema
    op.create_table(
        'alarm_history',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('rule_id', sa.Integer(), nullable=False),
        sa.Column('device_id', sa.Integer(), nullable=False),
        sa.Column('device_name', sa.String(255), nullable=False),
        sa.Column('metric_name', sa.String(255), nullable=False),
        sa.Column('value', sa.Float(), nullable=False),
        sa.Column('threshold', sa.Float(), nullable=False),
        sa.Column('operator', sa.String(10), nullable=False),
        sa.Column('severity', sa.String(20), nullable=False),
        sa.Column('triggered_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.Column('acknowledged', sa.Boolean(), nullable=False, server_default='0'),
        sa.Column('acknowledged_at', sa.DateTime(timezone=True), nullable=True),
        sa.Column('acknowledged_by_id', sa.Integer(), nullable=True),
        sa.Column('acknowledged_by', sa.String(255), nullable=True),  # Username cache
        sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.text('CURRENT_TIMESTAMP')),
        sa.ForeignKeyConstraint(['rule_id'], ['alarm_rules.id'], ondelete='CASCADE'),
        sa.ForeignKeyConstraint(['acknowledged_by_id'], ['users.id']),
        sa.PrimaryKeyConstraint('id')
    )

    # Create indexes for alarm_history
    op.create_index('idx_alarm_history_device_time', 'alarm_history', ['device_id', 'triggered_at'])
    op.create_index('idx_alarm_history_rule_time', 'alarm_history', ['rule_id', 'triggered_at'])
    op.create_index('idx_alarm_history_acknowledged', 'alarm_history', ['acknowledged', 'triggered_at'])
    op.create_index(op.f('ix_alarm_history_id'), 'alarm_history', ['id'])
    op.create_index(op.f('ix_alarm_history_rule_id'), 'alarm_history', ['rule_id'])
    op.create_index(op.f('ix_alarm_history_device_id'), 'alarm_history', ['device_id'])
    op.create_index(op.f('ix_alarm_history_metric_name'), 'alarm_history', ['metric_name'])
    op.create_index(op.f('ix_alarm_history_severity'), 'alarm_history', ['severity'])
    op.create_index(op.f('ix_alarm_history_triggered_at'), 'alarm_history', ['triggered_at'])
    op.create_index(op.f('ix_alarm_history_acknowledged'), 'alarm_history', ['acknowledged'])
    op.create_index(op.f('ix_alarm_history_acknowledged_by_id'), 'alarm_history', ['acknowledged_by_id'])


def downgrade() -> None:
    """Revert alarm tables to old schema."""
    # Drop new alarm tables
    op.drop_index(op.f('ix_alarm_history_acknowledged_by_id'), table_name='alarm_history')
    op.drop_index(op.f('ix_alarm_history_acknowledged'), table_name='alarm_history')
    op.drop_index(op.f('ix_alarm_history_triggered_at'), table_name='alarm_history')
    op.drop_index(op.f('ix_alarm_history_severity'), table_name='alarm_history')
    op.drop_index(op.f('ix_alarm_history_metric_name'), table_name='alarm_history')
    op.drop_index(op.f('ix_alarm_history_device_id'), table_name='alarm_history')
    op.drop_index(op.f('ix_alarm_history_rule_id'), table_name='alarm_history')
    op.drop_index(op.f('ix_alarm_history_id'), table_name='alarm_history')
    op.drop_index('idx_alarm_history_acknowledged', table_name='alarm_history')
    op.drop_index('idx_alarm_history_rule_time', table_name='alarm_history')
    op.drop_index('idx_alarm_history_device_time', table_name='alarm_history')
    op.drop_table('alarm_history')

    op.drop_index(op.f('ix_alarm_rules_created_by_id'), table_name='alarm_rules')
    op.drop_index(op.f('ix_alarm_rules_is_active'), table_name='alarm_rules')
    op.drop_index(op.f('ix_alarm_rules_severity'), table_name='alarm_rules')
    op.drop_index(op.f('ix_alarm_rules_metric_name'), table_name='alarm_rules')
    op.drop_index(op.f('ix_alarm_rules_device_name'), table_name='alarm_rules')
    op.drop_index(op.f('ix_alarm_rules_device_id'), table_name='alarm_rules')
    op.drop_index(op.f('ix_alarm_rules_id'), table_name='alarm_rules')
    op.drop_index('idx_alarm_rules_device_metric', table_name='alarm_rules')
    op.drop_table('alarm_rules')
